
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[MatchInfomations_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchInfomations_Insert]

IF OBJECT_ID(N'[dbo].[MatchInfomations_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchInfomations_Update]

IF OBJECT_ID(N'[dbo].[MatchInfomations_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchInfomations_Delete]

IF OBJECT_ID(N'[dbo].[MatchInfomations_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[MatchInfomations_Select]

--endregion

GO


--region [dbo].[MatchInfomations_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchInfomations_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdMatchInfomation int = null,
	@IdMatch int = null,
	@ClubMatchInfomation nvarchar(50) = null,
	@Goals int = null,
	@Procession float = null,
	@Shots int = null,
	@ShotOnTaget int = null,
	@ShotOut nchar(10) = null,
	@GoalkeeperBlock int = null,
	@Conner int = null,
	@Foul int = null,
	@Offside int = null,
	@Yellow int = null,
	@Red int = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[MatchInfomations]
					WHERE
					(
						(@IdMatchInfomation is null OR [MatchInfomations].[IdMatchInfomation] = @IdMatchInfomation)
						AND (@IdMatch is null OR [MatchInfomations].[IdMatch] = @IdMatch)
						AND (@ClubMatchInfomation is null OR [MatchInfomations].[ClubMatchInfomation] like @ClubMatchInfomation)
						AND (@Goals is null OR [MatchInfomations].[Goals] = @Goals)
						AND (@Procession is null OR [MatchInfomations].[Procession] = @Procession)
						AND (@Shots is null OR [MatchInfomations].[Shots] = @Shots)
						AND (@ShotOnTaget is null OR [MatchInfomations].[ShotOnTaget] = @ShotOnTaget)
						AND (@ShotOut is null OR [MatchInfomations].[ShotOut] like @ShotOut)
						AND (@GoalkeeperBlock is null OR [MatchInfomations].[GoalkeeperBlock] = @GoalkeeperBlock)
						AND (@Conner is null OR [MatchInfomations].[Conner] = @Conner)
						AND (@Foul is null OR [MatchInfomations].[Foul] = @Foul)
						AND (@Offside is null OR [MatchInfomations].[Offside] = @Offside)
						AND (@Yellow is null OR [MatchInfomations].[Yellow] = @Yellow)
						AND (@Red is null OR [MatchInfomations].[Red] = @Red)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[MatchInfomations].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdMatchInfomation' and @SortType = 1 THEN [MatchInfomations].[IdMatchInfomation] END ASC,
						CASE WHEN @SortBy = 'IdMatchInfomation' and @SortType = 0 THEN [MatchInfomations].[IdMatchInfomation] END DESC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 1 THEN [MatchInfomations].[IdMatch] END ASC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 0 THEN [MatchInfomations].[IdMatch] END DESC,
						CASE WHEN @SortBy = 'ClubMatchInfomation' and @SortType = 1 THEN [MatchInfomations].[ClubMatchInfomation] END ASC,
						CASE WHEN @SortBy = 'ClubMatchInfomation' and @SortType = 0 THEN [MatchInfomations].[ClubMatchInfomation] END DESC,
						CASE WHEN @SortBy = 'Goals' and @SortType = 1 THEN [MatchInfomations].[Goals] END ASC,
						CASE WHEN @SortBy = 'Goals' and @SortType = 0 THEN [MatchInfomations].[Goals] END DESC,
						CASE WHEN @SortBy = 'Procession' and @SortType = 1 THEN [MatchInfomations].[Procession] END ASC,
						CASE WHEN @SortBy = 'Procession' and @SortType = 0 THEN [MatchInfomations].[Procession] END DESC,
						CASE WHEN @SortBy = 'Shots' and @SortType = 1 THEN [MatchInfomations].[Shots] END ASC,
						CASE WHEN @SortBy = 'Shots' and @SortType = 0 THEN [MatchInfomations].[Shots] END DESC,
						CASE WHEN @SortBy = 'ShotOnTaget' and @SortType = 1 THEN [MatchInfomations].[ShotOnTaget] END ASC,
						CASE WHEN @SortBy = 'ShotOnTaget' and @SortType = 0 THEN [MatchInfomations].[ShotOnTaget] END DESC,
						CASE WHEN @SortBy = 'ShotOut' and @SortType = 1 THEN [MatchInfomations].[ShotOut] END ASC,
						CASE WHEN @SortBy = 'ShotOut' and @SortType = 0 THEN [MatchInfomations].[ShotOut] END DESC,
						CASE WHEN @SortBy = 'GoalkeeperBlock' and @SortType = 1 THEN [MatchInfomations].[GoalkeeperBlock] END ASC,
						CASE WHEN @SortBy = 'GoalkeeperBlock' and @SortType = 0 THEN [MatchInfomations].[GoalkeeperBlock] END DESC,
						CASE WHEN @SortBy = 'Conner' and @SortType = 1 THEN [MatchInfomations].[Conner] END ASC,
						CASE WHEN @SortBy = 'Conner' and @SortType = 0 THEN [MatchInfomations].[Conner] END DESC,
						CASE WHEN @SortBy = 'Foul' and @SortType = 1 THEN [MatchInfomations].[Foul] END ASC,
						CASE WHEN @SortBy = 'Foul' and @SortType = 0 THEN [MatchInfomations].[Foul] END DESC,
						CASE WHEN @SortBy = 'Offside' and @SortType = 1 THEN [MatchInfomations].[Offside] END ASC,
						CASE WHEN @SortBy = 'Offside' and @SortType = 0 THEN [MatchInfomations].[Offside] END DESC,
						CASE WHEN @SortBy = 'Yellow' and @SortType = 1 THEN [MatchInfomations].[Yellow] END ASC,
						CASE WHEN @SortBy = 'Yellow' and @SortType = 0 THEN [MatchInfomations].[Yellow] END DESC,
						CASE WHEN @SortBy = 'Red' and @SortType = 1 THEN [MatchInfomations].[Red] END ASC,
						CASE WHEN @SortBy = 'Red' and @SortType = 0 THEN [MatchInfomations].[Red] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[MatchInfomations]
			Where 
			(
				(@IdMatchInfomation is null OR [MatchInfomations].[IdMatchInfomation] = @IdMatchInfomation)
				AND (@IdMatch is null OR [MatchInfomations].[IdMatch] = @IdMatch)
				AND (@ClubMatchInfomation is null OR [MatchInfomations].[ClubMatchInfomation] like @ClubMatchInfomation)
				AND (@Goals is null OR [MatchInfomations].[Goals] = @Goals)
				AND (@Procession is null OR [MatchInfomations].[Procession] = @Procession)
				AND (@Shots is null OR [MatchInfomations].[Shots] = @Shots)
				AND (@ShotOnTaget is null OR [MatchInfomations].[ShotOnTaget] = @ShotOnTaget)
				AND (@ShotOut is null OR [MatchInfomations].[ShotOut] like @ShotOut)
				AND (@GoalkeeperBlock is null OR [MatchInfomations].[GoalkeeperBlock] = @GoalkeeperBlock)
				AND (@Conner is null OR [MatchInfomations].[Conner] = @Conner)
				AND (@Foul is null OR [MatchInfomations].[Foul] = @Foul)
				AND (@Offside is null OR [MatchInfomations].[Offside] = @Offside)
				AND (@Yellow is null OR [MatchInfomations].[Yellow] = @Yellow)
				AND (@Red is null OR [MatchInfomations].[Red] = @Red)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[MatchInfomations_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[MatchInfomations_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchInfomations_Insert]
	@IdMatchInfomation int OUTPUT,
	@IdMatch int,
	@ClubMatchInfomation nvarchar(50),
	@Goals int,
	@Procession float,
	@Shots int,
	@ShotOnTaget int,
	@ShotOut nchar(10),
	@GoalkeeperBlock int,
	@Conner int,
	@Foul int,
	@Offside int,
	@Yellow int,
	@Red int

AS


INSERT INTO [dbo].[MatchInfomations] 
(
	[IdMatch],
	[ClubMatchInfomation],
	[Goals],
	[Procession],
	[Shots],
	[ShotOnTaget],
	[ShotOut],
	[GoalkeeperBlock],
	[Conner],
	[Foul],
	[Offside],
	[Yellow],
	[Red]
)
VALUES 
(
	@IdMatch,
	@ClubMatchInfomation,
	@Goals,
	@Procession,
	@Shots,
	@ShotOnTaget,
	@ShotOut,
	@GoalkeeperBlock,
	@Conner,
	@Foul,
	@Offside,
	@Yellow,
	@Red
)

SET @IdMatchInfomation = SCOPE_IDENTITY()

--end [dbo].[MatchInfomations_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[MatchInfomations_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchInfomations_Update]
	@IdMatchInfomation int,
	@IdMatch int,
	@ClubMatchInfomation nvarchar(50),
	@Goals int,
	@Procession float,
	@Shots int,
	@ShotOnTaget int,
	@ShotOut nchar(10),
	@GoalkeeperBlock int,
	@Conner int,
	@Foul int,
	@Offside int,
	@Yellow int,
	@Red int
AS


UPDATE [dbo].[MatchInfomations] SET
	[IdMatch] = @IdMatch,
	[ClubMatchInfomation] = @ClubMatchInfomation,
	[Goals] = @Goals,
	[Procession] = @Procession,
	[Shots] = @Shots,
	[ShotOnTaget] = @ShotOnTaget,
	[ShotOut] = @ShotOut,
	[GoalkeeperBlock] = @GoalkeeperBlock,
	[Conner] = @Conner,
	[Foul] = @Foul,
	[Offside] = @Offside,
	[Yellow] = @Yellow,
	[Red] = @Red
WHERE
	[IdMatchInfomation] = @IdMatchInfomation

--end [dbo].[MatchInfomations_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[MatchInfomations_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[MatchInfomations_Delete]
	@IdMatchInfomation int
AS


DELETE FROM [dbo].[MatchInfomations]
WHERE
(
	[IdMatchInfomation] = @IdMatchInfomation
)

--end [dbo].[MatchInfomations_Delete]
--endregion

GO
--=========================================================================================--

